package com.singer.druid;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.singer.domain.Judge;
import com.singer.domain.SingerEntity;
import com.singer.piping.SeringQuery;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class QuerySinger implements SeringQuery {
    /**
     * 歌手查询
     */
    @Override
    public void query1() throws Exception{
        Properties prop = new Properties();

        prop.load(new FileInputStream("src/druid.properties"));

        DataSource data = DruidDataSourceFactory.createDataSource(prop);

        Connection connection = data.getConnection();
        String sql ="select*from t_singer";

         PreparedStatement sr= connection.prepareStatement(sql);
        ResultSet rs=sr.executeQuery();
         SingerEntity singer;
        List<SingerEntity> ser=new ArrayList<>();
        while (rs.next()){
          int id=   rs.getInt("id");
          String names= rs.getString("name");
          int age=rs.getInt("age");
          String sex=rs.getString("sex");
          String song=rs.getString("song");
          double score=rs.getDouble("score");
            singer=new SingerEntity();
            singer.setId(id);
            singer.setName(names);
            singer.setAge(age);
            singer.setSex(sex);
            singer.setSong(song);
            singer.setScore(score);
            ser.add(singer);
        }
        System.out.println(ser);
        rs.close();
        sr.close();
        connection.close();
    }

    @Override
    public void query2() throws Exception {
        Properties prop = new Properties();

        prop.load(new FileInputStream("src/druid.properties"));

        DataSource data = DruidDataSourceFactory.createDataSource(prop);

        Connection connection = data.getConnection();
        String sql ="select*from t_judge";

        PreparedStatement sr= connection.prepareStatement(sql);
        ResultSet we=sr.executeQuery();
        Judge judge;
        List<Judge> ser=new ArrayList<>();
        while (we.next()){
            int id= we.getInt("id");
            String names= we.getString("name");
            int age=we.getInt("age");
            String sex=we.getString("sex");
            judge= new Judge();
            judge.setId(id);
            judge.setName(names);
            judge.setAge(age);
            judge.setSex(sex);
            ser.add(judge);
        }
        System.out.println(ser);
        we.close();
        sr.close();
        connection.close();
    }
}
